<?php

require_once dirname(dirname(__FILE__)) . '/config/config.php';
require_once DOCUMENT_ROOT . DIR_SHARED_CONFIG . 'connection.php';
require_once DOCUMENT_ROOT . DIR_SHARED_LIBRARIES . 'lib_date.php';


class dao_ticket {

    //Methods
    public function __construct() {

    }

    public function get_all_dead() {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "SELECT cn.city_id AS city_id, ms.title AS movie_title, cn.name AS cinema_name, m.* "
                . "FROM tbl_ticket m LEFT JOIN tbl_movie ms ON m.movie_id = ms.id "
                . "LEFT JOIN tbl_cinema cn ON m.cinema_id = cn.id "
                . "WHERE 1 ";
        $now = lib_date::get_now();
        $date = lib_date::convert_to_sql_date($now, "Y-m-d H:i:s");
        $time = lib_date::convert_to_sql_time($now, "Y-m-d H:i:s");
        $query .= "AND NOT ("
                . "m.show_date > '" . $date . "' "
                . "OR (m.show_date = '" . $date . "' AND m.time_start > '" . $time . "') "
                . ") ";
        
        $result = mysqli_query($con, $query)
        or die("Query fail: " . mysqli_error());
        
        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }
        $db->close_connect();
        return $list;
    }
    
    public function get_all_live() {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "SELECT cn.city_id AS city_id, ms.title AS movie_title, cn.name AS cinema_name, m.* "
                . "FROM tbl_ticket m LEFT JOIN tbl_movie ms ON m.movie_id = ms.id "
                . "LEFT JOIN tbl_cinema cn ON m.cinema_id = cn.id "
                . "WHERE 1 ";
        $now = lib_date::get_now();
        $date = lib_date::convert_to_sql_date($now, "Y-m-d H:i:s");
        $time = lib_date::convert_to_sql_time($now, "Y-m-d H:i:s");
        $query .= "AND ("
                . "m.show_date > '" . $date . "' "
                . "OR (m.show_date = '" . $date . "' AND m.time_start > '" . $time . "') "
                . ") ";
        
        $result = mysqli_query($con, $query)
        or die("Query fail: " . mysqli_error());
        
        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }
        $db->close_connect();
        return $list;
    }
    
    public function get_all() {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "SELECT cn.city_id AS city_id, ms.title AS movie_title, cn.name AS cinema_name, m.* "
                . "FROM tbl_ticket m LEFT JOIN tbl_movie ms ON m.movie_id = ms.id "
                . "LEFT JOIN tbl_cinema cn ON m.cinema_id = cn.id";
        $result = mysqli_query($con, $query)
        or die("Query fail: " . mysqli_error());
        
        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }
        $db->close_connect();
        return $list;
    }
    
    public function get_all_by_filters($ticket_quantity, $city_id, $cinema_id, $movie_id, $show_date, $time_start) {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "SELECT cn.city_id AS city_id, ci.name AS city_name, ms.title AS movie_title, cn.name AS cinema_name, m.* "
                . "FROM tbl_ticket m LEFT JOIN tbl_movie ms ON m.movie_id = ms.id "
                . "LEFT JOIN tbl_cinema cn ON m.cinema_id = cn.id "
                . "LEFT JOIN tbl_city ci ON cn.city_id = ci.id "
                . "WHERE 1 ";
                
        
        $now = lib_date::get_now();
        $date = lib_date::convert_to_sql_date($now, "Y-m-d H:i:s");
        $time = lib_date::convert_to_sql_time($now, "Y-m-d H:i:s");
        $query .= "AND ("
                . "m.show_date > '" . $date . "' "
                . "OR (m.show_date = '" . $date . "' AND m.time_start > '" . $time . "') "
                . ") ";
        
        if (!empty($ticket_quantity) && $ticket_quantity > 0) {
        $query .= "AND m.available_seats >= " . $ticket_quantity . " ";
        }
        if (!empty($city_id) && $city_id > 0) {
        $query .= "AND cn.city_id = " . $city_id . " ";
        }
        if (!empty($cinema_id) && $cinema_id > 0) {
        $query .= "AND m.cinema_id = " . $cinema_id . " ";
        }
        if (!empty($movie_id) && $movie_id > 0) {
        $query .= "AND m.movie_id = " . $movie_id . " ";
        }
        if (!empty($show_date)) {
        $query .= "AND m.show_date = '" . $show_date . "' ";
        }
        if (!empty($time_start)) {
        $query .= "AND m.time_start = '" . $time_start . "' ";
        }
        $query .= "ORDER BY m.cinema_id ASC, m.time_start ASC ";
        
        $result = mysqli_query($con, $query)
        or die("Query fail: " . mysqli_error());
        
        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }
        $db->close_connect();
        return $list;
    }
    
    public function get_all_by_status_id($status_id) {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "SELECT ms.name AS status_name, m.* "
                . "FROM tbl_ticket m LEFT JOIN tbl_ticket_status ms ON m.status_id = ms.id "
                . "WHERE ms.id = " . $status_id;
        $result = mysqli_query($con, $query)
        or die("Query fail: " . mysqli_error());
        
        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }
        $db->close_connect();
        return $list;
    }

    
    
    public function update_available($id, $available) {
        $db = new connection();
        $con = $db->open_connect();
        
        if ($id != 0) {
            $query = "UPDATE tbl_ticket SET "
                    . "available_seats = '" . $available . "' "
                    . "WHERE id = " . $id;
        }
        mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $db->close_connect();
        return TRUE;
    }
    
    public function save($id, $name, $show_date, 
                $time_start, $duration, $movie_id, $cinema_id, $price, $total_seats, $available_seats) {
        $db = new connection();
        $con = $db->open_connect();
        
        if ($id == 0) {
            $query = "INSERT INTO tbl_ticket(name, show_date, time_start, duration, movie_id, cinema_id, price, total_seats, available_seats) VALUES (
                '" . $name . "','" . $show_date . "','" . $time_start . "'," . $duration . "," . $movie_id . "," . $cinema_id . "," . $price . "," . $total_seats . "," . $available_seats . ")";
        } else {// no update $total_seats, $available_seats
            $query = "UPDATE tbl_ticket SET "
                    . "name = '" . $name . "',"
                    . "show_date = '" . $show_date . "',"
                    . "time_start = '" . $time_start . "',"
                    . "duration = " . $duration . ","
                    . "movie_id = " . $movie_id . ","
                    . "cinema_id = " . $cinema_id . ","
                    . "price = " . $price . " "
                    . "WHERE id = " . $id;
        }
        mysqli_query($con, $query) or die("Query fail: " . mysqli_error());
        $inserted_id = mysqli_insert_id($db->con);
        $db->close_connect();
        return $inserted_id;
    }
    
    public function get_by_id($id) {
        $db = new connection();
        $con = $db->open_connect();

        $query = "SELECT ms.title AS movie_title, cn.name AS cinema_name, m.* "
                . "FROM tbl_ticket m LEFT JOIN tbl_movie ms ON m.movie_id = ms.id "
                . "LEFT JOIN tbl_cinema cn ON m.cinema_id = cn.id WHERE m.id = " . $id;

//        echo $query;
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $row = mysqli_fetch_array($result);

        $db->close_connect();
        return $row;
    }

    public function delete($id) {
        $db = new connection();
        $con = $db->open_connect();

        $query = "DELETE FROM tbl_ticket WHERE id = " . $id;
        mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $db->close_connect();
        return TRUE;
    }
    
    public function get_first() {
        $db = new connection();
        $con = $db->open_connect();

        $query = "SELECT * FROM tbl_ticket LIMIT 0, 1";
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $row = mysqli_fetch_array($result);

        $db->close_connect();
        return $row;
    }
    
    public function get_id_by_name($name) {
        $db = new connection();
        $con = $db->open_connect();

        $query = "SELECT id FROM tbl_ticket WHERE UPPER(name) = UPPER('" . $name ."') LIMIT 0, 1";
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $row = mysqli_fetch_array($result);

        $db->close_connect();
        return $row;
    }
}
